const Router = require('koa-router')

const {success} = require('../../lib/helper')

const {Sequelize} = require('sequelize')

const {
    Auth
} = require('../../../middlewares/auth')

const {
    MainContentValidator
} = require('../../validators/validator')

const {Equipment} = require("../../models/equipment");
const {Booking} = require("../../models/booking");
const {sequelize} = require("../../../core/db");

const router = new Router({
    prefix: '/v1/equipment'
})


/**
 * 用户上传附件的接口 （基本信息+摘要文章）
 * lastname   名字
 * firstname  姓氏
 * email      邮箱
 * phone      手机号
 * organ      公司/组织
 * country    国家
 * abstract   摘要
 * article    文章
 */
router.post('/add', async (ctx) => {
    const v = await new MainContentValidator().validate(ctx)
    const equipment = {
        status: v.get('body.status'),
        code: v.get('body.code'),
        name: v.get('body.name'),
        type: v.get('body.type'),
        original: v.get('body.original'),
        btime: v.get('body.btime'),
        cumulative: v.get('body.cumulative'),
        pic: v.get('body.pic'),
    }

    await Equipment.create(equipment)
    success()
})


//查询全部
router.get('/listall', async ctx => {
    // 直接调用 Equipment.findAll 来获取所有设备数据
    const equipments = await Equipment.findAll();

    // 使用 success 函数（假设它返回一个 JSON 响应）来返回数据
    ctx.body = success({
        data: equipments
    });
});




/*
* 分页查询
*
* */
// 假设 Equipment 是一个 Sequelize 模型，已经定义了所有必要的CRUD方法。


router.get('/list', async ctx => {
    const v = await new MainContentValidator().validate(ctx);
    // 从查询字符串中获取分页参数
    const {page = 1, pagesize = 10} = ctx.query;

    // 验证分页参数是否为正整数，并设置合理的默认值
    const validatedPage = Math.max(Number(page) || 1, 1);
    const validatedPagesize = Math.max(Number(pagesize) || 10, 1);

    // 计算偏移量
    const offset = (validatedPage - 1) * validatedPagesize;

    // 使用Sequelize的findAndCountAll方法进行分页查询
    const result = await Equipment.findAndCountAll({
        limit: validatedPagesize,
        offset: offset,
    });

    console.log('result.rows= ', result.rows.Equipment)
    let finResult = {
        data: result.rows,
        total: result.count,
        page: validatedPage,
        pagesize: validatedPagesize,
    }

    success(finResult)
})

// 查询所有设备数据
router.get('/countTime', async ctx => {
    const v = await new MainContentValidator().validate(ctx); // 假设这是你的验证逻辑

    // 使用sequelize.query执行原生SQL查询
    const result = await db.query(`  
        SELECT  
            eq.id,  
            eq.name,  
            eq.code,  
            eq.status,  
            eq.created_at,  
            eq.type,  
            eq.original,  
            eq.btime,  
            COALESCE(SUM(TIMESTAMPDIFF(SECOND,  
                            CASE WHEN b.start < NOW() THEN b.start ELSE NOW() END,  
                            CASE WHEN b.end > NOW() THEN NOW() ELSE b.end END  
                        ))/ 3600, 0) AS usetime  
        FROM  
            ${Equipment.tableName} eq  
        LEFT JOIN  
            ${Booking.tableName} b ON eq.id = b.mid  
            AND b.end <= NOW()  //只考虑结束时间在当前时间之前的预约  
        GROUP BY  
            eq.id, eq.name  
    `, {
        type: db.QueryTypes.SELECT // 告诉Sequelize这是一个SELECT查询
    });

    // 你可以在这里添加一些额外的处理，比如格式化数据等（如果需要）

    // 构造响应数据
    // let finResult = {
    //     data: result // 直接使用查询结果作为数据
    // };

    // 假设success是一个用于发送成功响应的函数
    success(result)
})


// 根据id删除数据的接口
router.post('/del', async (ctx) => {
    const v = await new MainContentValidator().validate(ctx);
    const id = v.get('body.id');
    await Equipment.deleteMoudle(id);
    success();
})


router.post('/update', async (ctx) => {
    const v = await new MainContentValidator().validate(ctx)

    let id = v.get('body.id')
    let status = v.get('body.status')
    let code = v.get('body.code')
    let name = v.get('body.name')
    let type = v.get('body.type')
    let original = v.get('body.original')
    let btime = v.get('body.btime')
    let cumulative = v.get('body.cumulative')
    let pic = v.get('body.pic')

    await Equipment.updateMoudle(id, status, code, name, type, original, btime, cumulative, pic)
    success()
})


module.exports = router
